var express = require('express');
var router = express.Router();
var mysql = require('mysql');
var moduleDB = require('./db/db')  // 引入数据模型
var $sql = require('./db/mySQL')   // 引入sql语句对象

const multer  = require('multer')
// //接收到的文件放uploads文件夹
const upload = multer({ dest: 'uploads/' })

// 连接 mysql 数据库
var conn = mysql.createConnection(moduleDB.mysql);
conn.connect();

//添加头像
router.post('/addPic',upload.single('icon'),(req,res)=>{
  const {file,body} = req
  // console.log(file,body);
  const username = body.name;
  if(file == undefined){
    res.send({
      "meta":{
        "msg":"新增失败，参数缺失",
        "status":400
      }
    })
  }
  else{
    const sql = 'update user set picURL =? where username =?'
    conn.query(sql,[file.filename,username],(error,results)=>{
      if(error){
        res.send({
          "meta":{
            "msg":"请求错误",
            "status":400
          }
        })
        return
      } 
      if(results.affectedRows!==1){
        res.send({
          "meta": {
            "msg": "添加不到pic数据",
            "status": 403
          }
        })
        return 
      }else{
        res.send({
          "data": results,
          "meta": {
            "msg": "添加pic成功",
            "status": 200
          }
        })
      }
    
    })
  }
})

//展示头像
router.get('/showPic',(req,res)=>{
  const getname = req.query;
  // console.log(req.query);
  const sql = 'select picURL from user where username=?';
  conn.query(sql,getname.username,(error,results)=>{
    if(error){
      res.send({
        "meta":{
          "msg":"请求错误",
          "status":400
        }
      })
      return
    }
    if(results.length===0){
      res.send({
        "meta":{
          "msg":"暂无数据",
          "status":200
        }
      })
    }else{
      res.send({
        "data": results,
          "meta": {
            "msg": "查询pic成功",
            "status": 200
          }
      })
    }
  })
})

//删除poke 
router.post('/deletePoke',(req,res)=>{
  const id = req.body.id;
  const sql = 'delete from poke where id =?';
  conn.query(sql,id,(error,results)=>{
    if(error){
      res.send({
        "meta":{
          "msg":"请求出错了！",
          "status":400
        }
      })
    }
    if(results.affectedRows!==1){
      res.send({
        "meta":{
          "msg":"处理失败！",
          "status":403
        }
      })
      return;
    }else{
      res.send({
        "data":results,
        "meta":{
          "msg":"处理成功！",
          "status":200
        }
      })
    }
  })
  
})

//更新poke
router.post('/updatePoke',(req,res)=>{
  const id = req.body.id;
  const title = req.body.title;
  const content = req.body.content;
  const tip = req.body.tip;
  const author = req.body.author;
  const sql = 'update poke set title =?,content =?,tip =?,author =? where id =?';
  conn.query(sql,[title,content,tip,author,id],(error,results)=>{
    if(error){
      console.log(error);
      res.send({
        "meta":{
          "msg":"请求错误",
          "status":400
        }
      })
      return
    }
    if(results.affectedRows!==1){
      res.send({
        "meta": {
          "msg": "找不到updateUser数据",
          "status": 403
        }
      })
      return 
    }else{
      res.send({
        "data": results,
        "meta": {
          "msg": "添加updateUser成功",
          "status": 200
        }
      })
    }
  })
})

// 查找poke数据
router.post('/findPoke',(req,res)=>{
 
  const findkey = req.body
  const sql = 'select * from poke where title =?'
  // var key = ''
  // var sql = ''
  // if(findkey.title == ''){
  //   sql = 'select * from poke where author =?' ;
  //   key = findkey.author;
  // }else{
  //   sql = 'select * from poke where title =?' ;
  //   key = findkey.title;
  // }
  // console.log(sql,key);

  conn.query(sql,findkey.title,(error,results9)=>{
    if(error){
      console.log(error);
      res.send({
        "meta":{
          "msg":"请求错误",
          "status":400
        }
      })
    }
    if(results9[0]==undefined){

      res.send({
        "meta": {
          "msg": "找不到poke数据",
          "status": 403
        }
      })
    }else{
      res.send({
        "data": results9,
        "meta": {
          "msg": "数据查询成功",
          "status": 200
        }
      })
    }
  })
})

// 更改user数据
router.post('/updateUser',(req,res)=>{
  const getUser = req.body;
  const sql = 'update user set username =?,password =?,email =? where username =?';
  conn.query(sql,[getUser.username,getUser.password,getUser.email,getUser.oldname],(error,results7)=>{
    if(error){
      console.log(error);
      res.send({
        "meta":{
          "msg":"请求错误",
          "status":400
        }
      })
      return
    }
    if(results7.affectedRows!==1){
      res.send({
        "meta": {
          "msg": "找不到updateUser数据",
          "status": 403
        }
      })
      return 
    }else{
      res.send({
        "data": results7,
        "meta": {
          "msg": "添加updateUser成功",
          "status": 200
        }
      })
    }
  })
})


// 新建Poke
router.post('/insertPoke',(req,res)=>{
  const getPoke = req.body
  // console.log(req.body);
  const title = getPoke.title
  const content = getPoke.content
  const tip = getPoke.tip
  const author = getPoke.author
  const sql = 'insert into poke (title,content,tip,author) values (?,?,?,?)'
  conn.query(sql,[title,content,tip,author],(error,results5)=>{
    if(error){
      console.log(error);
      res.send({
        "meta":{
          "msg":"请求错误",
          "status":400
        }
      })
      return
    }
    if(results5.affectedRows!==1){
      res.send({
        "meta": {
          "msg": "找不到poke数据",
          "status": 403
        }
      })
      return 
    }else{
      res.send({
        "data": results5,
        "meta": {
          "msg": "添加poke成功",
          "status": 200
        }
      })
    }
  
  })
})

// 获取poke文章数据
router.get('/show',(req,res)=>{
  const sql = 'select * from poke';
  conn.query(sql,(error,results)=>{
    if(error){
      res.send({
        "meta": {
          "msg": "poke请求错误！",
          "status": 400
        }
      })
      return;
    }
    if (results[0] === undefined) {
      // 用户不存在
      res.send({
        "meta": {
          "msg": "找不到poke数据",
          "status": 403
        }
      })
    }else{
      res.send({
        "data": results,
        "meta": {
          "msg": "登录成功",
          "status": 200
        }
      })
    }
  })
})


// 注册
router.post('/register', (req, res) => {
  // res.send("纳米好")
  // 1、获取表单提交数据
  const user = req.body;
  // console.log(user.username);
  // 获取对于的sql语句
  const selUser_sql = $sql.usersql.select + " where username = '" + user.username + "' or email = '" + user.email + "' "
  // console.log(reg_sql);
  const addUser_sql = $sql.usersql.add;
  // 2、 先查询是否存在该用户名
  conn.query(selUser_sql, user, (error, result1) => {
    if (error) {
      // console.log(error);
      res.send({
        "meta": {
          "msg": "请求错误！",
          "status": 400
        }
      })
      return;
    }
    // console.log(user);
    if (result1.length != 0 && user.username === result1[0].username) {
      res.send({
        "meta": {
          "msg": "用户名已存在",
          "status": 403
        }
      });  // 表示用户名已存在
    }
    else if (result1.length != 0 && user.email === result1[0].email) {
      res.send({
        "meta": {
          "msg": "邮箱已存在",
          "status": 403
        }
      });  // 表示邮箱已存在
    }
    else {
      // 如果不存在就进行 插入数据
      conn.query(addUser_sql, [user.username, user.email, user.password], (err, result2) => {
        if (err) {
          // console.log(err);
          res.send({
            "meta": {
              "msg": "请求错误！",
              "status": 400
            }
          })
        } else {
          // console.log(result2);
          res.send({
            "data": {
              "username": user.username,
              "email": user.email,
              "password": user.password
            },
            "meta": {
              "msg": "注册成功",
              "status": 200
            }
          });
        }
      })
    }
  })
})

// 登录：
router.post('/login', (req, res) => {
  const getUser = req.body;
  // 根据email查询用户进行登录
  // console.log(getUser);
  const selEmail_sql = $sql.usersql.select + " where email = '" + getUser.email + "'";
  
  // 进行数据查询
  conn.query(selEmail_sql, getUser.email, (error, results1) => {
    if (error) {
      console.log(error);
      res.send({
        "meta": {
          "msg": "请求错误！",
          "status": 400
        }
      })
      return
    }
    // 如果查询不到登录用户
    if (results1[0] === undefined) {
      // 用户不存在
      res.send({
        "meta": {
          "msg": "邮箱或者密码错误",
          "status": 403
        }
      })
    }
    else {
      // 登录根据email和password来登录
      if (results1[0].email == getUser.email && results1[0].password == getUser.password) {
        // 表示用户存在且邮箱和密码都输入正确
        res.send({
          "data": results1[0],
          "meta": {
            "msg": "登录成功",
            "status": 200
          }
        })
      } else {
        res.send({
          // 用户存在，但是输入的密码错误
          "meta": {
            "msg": "邮箱或者密码错误",
            "status": 403
          }
        })
      }
    }
  })
})


// conn.end()
module.exports = router